package com.sunwayworld.basemodule.common.eptstorage.db;

import com.sunwayworld.basemodule.common.eptstorage.db.bean.LimsDbconnBean;
import com.sunwayworld.basemodule.common.eptstorage.db.service.LimsDbconnService;
import com.sunwayworld.framework.utils.ObjectUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 使用dbtemplate
 * @Autowired
 * @Qualifier("sourceName")
 * private JdbcTemplate sourceNameJdbcTemplate;
 */
@Service
public class DbHelper {
    private static final Logger logger = LogManager.getLogger(DbHelper.class);

    @Autowired
    @Lazy
    private LimsDbconnService limsDbconnService;
    @Autowired
    private DynamicBeanHanderService dynamicBeanHanderService;

    //注入db jdbctemplate
    public void register(){
        List<LimsDbconnBean> dbconnBeans = limsDbconnService.selectAll();
        List<LimsDbconnBean> beanList = dbconnBeans.stream().filter(f ->
                !ObjectUtils.isEmpty(f.getSourceName())
                && !ObjectUtils.isEmpty(f.getUrl())
                && !ObjectUtils.isEmpty(f.getUsername())
                && !ObjectUtils.isEmpty(f.getPassword())).collect(Collectors.toList());
        for (LimsDbconnBean limsDbconnBean : beanList) {
            register(limsDbconnBean);
        }
    }

    public boolean register(LimsDbconnBean limsDbconnBean) {
        DriverManagerDataSource datasource = new DriverManagerDataSource();
        datasource.setUsername(limsDbconnBean.getUsername());
        datasource.setPassword(limsDbconnBean.getPassword());
        datasource.setUrl(limsDbconnBean.getUrl());
        String drive = "";
        String mysql = "com.mysql.cj.jdbc.Driver";
        String sqlserver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String oracle = "oracle.jdbc.OracleDriver";
        String postgresql = "org.postgresql.Driver";
        if(limsDbconnBean.getUrl().toLowerCase().contains("mysql")){
            drive = mysql;
        }else if(limsDbconnBean.getUrl().toLowerCase().contains("sqlserver")){
            drive = sqlserver;
        }else if(limsDbconnBean.getUrl().toLowerCase().contains("oracle")){
            drive = oracle;
        } else if (limsDbconnBean.getUrl().toLowerCase().contains("postgresql")) {
            drive = postgresql;
        } else {
            logger.error("Url={} is not support", limsDbconnBean.getUrl());
            return false;
        }
        datasource.setDriverClassName(drive);

        String beanId = limsDbconnBean.getSourceName();
        try{
            dynamicBeanHanderService.register(beanId,JdbcTemplate.class,datasource);
            boolean test = test(limsDbconnBean);
            logger.info("init db={},result={}", limsDbconnBean.getSourceName(),test);
            return test;
        }catch (Throwable s){
            logger.error("init db={},error={}", limsDbconnBean.getSourceName(),s);
            return false;
        }
    }

    //测试链接
    public boolean test(LimsDbconnBean dbconnBean){
        if(ObjectUtils.isEmpty(dbconnBean.getSourceName())){
            logger.error("SourceName is null");
            return false;
        }
        Connection connection = null;
        try {
            Object serviceBean = dynamicBeanHanderService.getBean(dbconnBean.getSourceName());
            if(serviceBean==null){
                logger.error("SourceName={} is not found",serviceBean);
                return false;
            }
            JdbcTemplate jdbcTemplate = (JdbcTemplate) serviceBean;
            connection = jdbcTemplate.getDataSource().getConnection();
        } catch (Exception throwables) {
            logger.error("dbconnBean={},test connect error={}",dbconnBean.getSourceName(),throwables);
            dynamicBeanHanderService.destory(dbconnBean.getSourceName());
            return false;
        }finally {
            try {
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return  true;
    }

    public JdbcTemplate getTemplate(String name){
        if(ObjectUtils.isEmpty(name)){
            logger.error("SourceName is null");
            return null;
        }
        Object serviceBean = dynamicBeanHanderService.getBean(name);
        if(serviceBean==null){
            logger.error("SourceName={} is not found",serviceBean);
            return null;
        }
        return (JdbcTemplate) serviceBean;
    }

    public JdbcTemplate getTemplate(LimsDbconnBean dbconnBean){
        if(ObjectUtils.isEmpty(dbconnBean.getSourceName())){
            logger.error("SourceName is null");
            return null;
        }
        Object serviceBean = dynamicBeanHanderService.getBean(dbconnBean.getSourceName());
        if(serviceBean==null){
            logger.error("SourceName={} is not found",serviceBean);
            return null;
        }
        return (JdbcTemplate) serviceBean;
    }

    public <T> List<T> query(LimsDbconnBean dbconnBean,String sql, T t){
        JdbcTemplate jdbcTemplate = this.getTemplate(dbconnBean);
        logger.info("sourceName:{}",dbconnBean.getSourceName());
        return query(jdbcTemplate,sql,t);
    }

    public <T> List<T> query(JdbcTemplate jdbcTemplate,String sql, T t){
        logger.info("sql:{}",sql);
        return (List<T>) jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(t.getClass()));
    }

    public List<Map<String,Object>> query(LimsDbconnBean dbconnBean, String sql){
        JdbcTemplate jdbcTemplate = this.getTemplate(dbconnBean);
        logger.info("sourceName:{}",dbconnBean.getSourceName());
        return query(jdbcTemplate,sql);
    }

    public List<Map<String,Object>> query(JdbcTemplate jdbcTemplate,String sql){
        logger.info("sql:{}",sql);
        return jdbcTemplate.queryForList(sql);
    }
}
